﻿<SQLFunctionSpec
    xmlns="bb_appfx_sqlfunction"
	xmlns:c="bb_appfx_commontypes" 
	ID="7a6d72d0-c9b9-4e68-8e89-0f7ab25bccd9"
	Name="Revenue for Constituent over the last N days, for a designation"
	Description="Given a constituent, a designation, and a number of days, return the $ amount of revenue given by that constituent to that designation over the last N days"
	Author="Blackbaud Professional Services"
	DBFunctionName="USR_UFN_REVENUE_FOR_CONSTITUENTDESIGNATION_OVER_LASTNDAYS"
	>

	<CreateFunctionSQL>
		<![CDATA[
create function dbo.USR_UFN_REVENUE_FOR_CONSTITUENTDESIGNATION_OVER_LASTNDAYS(
  @CONSTITUENTID uniqueidentifier
 ,@DESIGNATIONID uniqueidentifier
 ,@REVENUEDAYSBACK INT = 365
)
returns money
with execute as caller
as begin

  declare @result money = (
    select
      SUM(rsplit.AMOUNT)
    from
      revenue rev
      inner join
      revenuesplit rsplit
      on rev.id = rsplit.revenueid
    where 1=1
      and rev.transactiontype = 'Payment'
      and rev.CONSTITUENTID = @constituentid
      and ((@designationid is null) or (rsplit.DESIGNATIONID = @designationid))  --null designation means "all designations"
      and ((@revenuedaysback is null) or (rev.DATE > (getdate() - @revenuedaysback))) --null daysback means "forever"
      );
  return isnull(@result,0);
end
]]>
	</CreateFunctionSQL>
</SQLFunctionSpec>